package com.bw.dao;

import com.bw.bean.Dmp;
import com.bw.bean.Emp;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface EmpDao {
    //按照省份展示总成交额指标sql  （热力地图）
    @Select("select b.`name`,sum(o.final_total_amount) `value` from order_info o\n" +
            "LEFT JOIN base_province b on o.province_id=b.id\n" +
            "group by b.`name`")
    List<Emp> getList1();

    //#按品牌展示销售排行 sql         （柱状图）
    @Select("select b.`name`,sum(o.final_total_amount) `value` from order_info o\n" +
            "LEFT JOIN order_detail d on  d.order_id=o.id\n" +
            "LEFT JOIN sku_info s on d.sku_id=s.id\n" +
            "LEFT JOIN base_trademark b on s.tm_id=b.id\n" +
            "where b.`name` is not null group by b.`name` ")
    List<Emp> getList2();

    //#按各品类展示销售占比 sql       （轮播饼图）
    @Select("select c1.`name`,sum(o.final_total_amount) `value` from order_info o\n" +
            "LEFT JOIN order_detail d on d.order_id=o.id\n" +
            "LEFT JOIN sku_info s on d.sku_id=s.id\n" +
            "LEFT JOIN base_category3 c3 ON s.category3_id=c3.id\n" +
            "LEFT JOIN base_category2 c2 ON c3.category2_id=c2.id\n" +
            "LEFT JOIN base_category1 c1 ON c2.category1_id=c1.id\n" +
            "where c1.`name` is not NULL\n" +
            "group by c1.`name`")
    List<Emp> getList3();


    //新老用户对比sql        （轮播表格）
    @Select("select * from(select '新用户'name,count(*) value from user_info where create_time between '2024-05-01' and '2024-05-31') a\n" +
            "UNION\n" +
            "select * from(select '老用户'name,count(*) value from user_info where create_time <'2024-05-01')b")
    List<Emp> getList4();

    // 按照第二大类交易额排名 sql  （柱状图）
    @Select("select c2.`name`,sum(o.final_total_amount) `value` from order_info o\n" +
            "LEFT JOIN order_detail d on d.order_id=o.id\n" +
            "LEFT JOIN sku_info s on d.sku_id=s.id\n" +
            "LEFT JOIN base_category3 c3 ON s.category3_id=c3.id\n" +
            "LEFT JOIN base_category2 c2 ON c3.category2_id=c2.id\n" +
            "where c2.`name` is not null \n" +
            "group by c2.`name`")
    List<Emp> getList5();

    // 按年龄段分析订单指标 sql   （轮播饼图）
    @Select("select CONCAT(SUBSTRING(u.birthday,3,1),'0后') `name`,sum(o.final_total_amount) `value` from order_info o\n" +
            "LEFT JOIN user_info u on o.user_id=u.id\n" +
            "group by CONCAT(SUBSTRING(u.birthday,3,1),'0后')")
    List<Emp> getList6();

    // 按品牌与性别排名  sql （轮播表格展示）
    @Select("select b.`name`,if(u.gender='M','男','女') `sex`,sum(o.final_total_amount) `value` from user_info u\n" +
            "LEFT JOIN order_info o ON o.user_id=u.id\n" +
            "LEFT JOIN order_detail d ON d.order_id=o.id\n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id\n" +
            "LEFT JOIN base_trademark b ON s.tm_id=b.id\n" +
            "where b.`name` is not null\n" +
            "group by b.`name`")
    List<Dmp> getList7();

    // 总成交金额的存储过程 sql  (数字翻码牌)
    @Select("call getsum();")
    Double getList8();

    // 按SPU 分组订单金额 sql  ( 轮播表格展示  )
    @Select("select p.spu_name name,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON d.order_id=o.id\n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id\n" +
            "LEFT JOIN spu_info p ON s.spu_id=p.id\n" +
            "where p.spu_name is not null\n" +
            "group by p.spu_name")
    List<Emp> getList9();




}
